package com.book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.book.domain.Book;
import com.book.domain.SplitPage;
import com.book.util.DBUtil;

public class BookDao {

	public void addBook(Book book) {
		Connection connection = DBUtil.getConnection();
		String sql = "insert into t_book(bookname,authorname,publishername,bookcatalog,bookimgurl,bookprice,bookoutline) values(?,?,?,?,?,?,?) ";
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBookname());
			preparedStatement.setString(2, book.getAuthorname());
			preparedStatement.setString(3, book.getPublishername());
			preparedStatement.setString(4, book.getBookcatalog());
			preparedStatement.setString(5, book.getBookimgurl());
			preparedStatement.setFloat(6, book.getBookprice());
			preparedStatement.setString(7, book.getBookoutline());

			preparedStatement.executeUpdate();
			System.out.println("图书成功插入");
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBUtil.closeConnection(preparedStatement, null, connection);
		}

	}

	public List<Book> listAllBooks() {
		List<Book> bookslist = new ArrayList<Book>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select * from t_book";
		try {
			preparedStatement = connection.prepareCall(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				Book book = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getFloat("bookprice"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));
				bookslist.add(book);
				System.out.println(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConnection(preparedStatement, resultSet, connection);
		}
		return bookslist;
	}

	public void delteBook(Book book) {
		Connection connection = DBUtil.getConnection();
		String sql = "delete from t_book where id=?";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, book.getId());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConnection(preparedStatement, null, connection);
		}
	}

	public Book selectBook(Book book) {
		Connection connection = DBUtil.getConnection();
		String sql = "select * from t_book where id=?";
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Book book1 = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, book.getId());
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {

				book1 = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getFloat("bookprice"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));

				System.out.println(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return book1;
	}

	public List<Book> getSortBooks(Book book) {
		List<Book> bookslist = new ArrayList<Book>();
		Connection connection = DBUtil.getConnection();
		String sql = "select * from t_book where bookname=? or authorname=? or publishername=? or bookcatalog=? or bookprice=? or bookoutline=? ";
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Book book1=null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBookname());
			preparedStatement.setString(2, book.getAuthorname());
			preparedStatement.setString(3, book.getPublishername());
			preparedStatement.setString(4, book.getBookcatalog());
			preparedStatement.setFloat(5, book.getBookprice());
			preparedStatement.setString(6, book.getBookoutline());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				book1 = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getFloat("bookprice"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));
                bookslist.add(book1);
				System.out.println(book1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bookslist;
	}
	public List<Book> splitpagebook(SplitPage splitPage,Book book){
		List<Book> bookslist=new ArrayList<Book>();
	Connection connection=	DBUtil.getConnection();
	String sql="select * from  (select *,ROW_NUMBER()over(order by id) as rownumber from t_book ) as t where rownumber between ? and ?";	
	PreparedStatement preparedStatement=null;
	ResultSet resultSet=null;
	Book book1=null;
	try {
	preparedStatement=	connection.prepareStatement(sql);
	preparedStatement.setInt(1, splitPage.getStartrecord());
	preparedStatement.setInt(2, splitPage.getStoprecord());
	resultSet=preparedStatement.executeQuery();
	while (resultSet.next()) {

		book1 = new Book(resultSet.getInt("id"),
				resultSet.getString("bookname"),
				resultSet.getString("authorname"),
				resultSet.getString("publishername"),
				resultSet.getString("bookcatalog"),
				resultSet.getFloat("bookprice"),
				resultSet.getString("bookimgurl"),
				resultSet.getString("bookoutline"));
        bookslist.add(book1);
		System.out.println(book1);
	}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		return bookslist;
	}
}
